package com.turing.post.cardmanagement.impl;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.ejb.EJB;
import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.cardmanagement.util.BranchCardStock;
import com.turing.post.cardmanagement.util.CardClassicDao;
import com.turing.post.cardmanagement.util.CardParValueDao;
import com.turing.post.cardmanagement.util.CardStock;
import com.turing.post.cardmanagement.util.CardStockClassicInfo;
import com.turing.post.cardmanagement.util.CardStockDao;
import com.turing.post.organization.util.SysBranch;
import com.turing.post.organization.util.SysBranchDao;
import com.turing.post.util.Config;
import com.turing.post.util.GeneralDao;
import com.turing.post.util.PCPSApplicationException;
import com.turing.post.util.PCPSUtil;
import com.turing.post.util.PCPSWebInput;

@Stateless(mappedName = "cardmanagement.impl.CardStockDaoImpl")
@Remote
@Local
public class CardStockDaoImpl implements CardStockDao {

	/**
	 * 实体管理器
	 */
	@PersistenceContext
	protected EntityManager _em;

	@EJB(mappedName = "organization.impl.SysBranchDaoImpl")
	private SysBranchDao SysBranchDaoImpl;

	@EJB(mappedName = "cardmanagement.impl.CardClassicDaoImpl")
	private CardClassicDao CardClassicDaoImpl;

	@EJB(mappedName = "cardmanagement.impl.CardParValueDaoImpl")
	private CardParValueDao CardParValueDaoImpl;

	@SuppressWarnings("unchecked")
	@Override
	public CardStock getCardStockByCardseqno(String cardseqno) {
		return _em.find(CardStock.class, cardseqno);
	}

	@SuppressWarnings("unchecked")
	@Override
	public CardStock getCardStockByCardSeqNoOrCardNo(String cardNo) {
		String sql = "select t.* from cardstock t where t.cardSeqNo = ?1 or t.cardNo = ?1";
		Query query = _em.createNativeQuery(sql, CardStock.class);
		query.setParameter(1, cardNo);
		List<CardStock> list = query.getResultList();
		if (list == null || list.size() != 1)
			return null;
		CardStock cs = list.get(0);
		_em.refresh(cs);
		return getCardStockByCardseqno(cs.getCardSeqNo());
	}

	@Override
	public void updateCardStock(CardStock cs) throws PCPSApplicationException {
		GeneralDao.update(_em, cs);
	}

	@Override
	public void deleteCardStock(String cardseqno)
			throws PCPSApplicationException {
		GeneralDao.remove(_em, getCardStockByCardseqno(cardseqno));
	}

	/**
	 * @删除操作
	 * @param batchno
	 * @param startCardSeqNo
	 * @param endCardSeqNo
	 */
	@Override
	public Integer deleteCardStock(String batchno, String startCardSeqNo,
			String endCardSeqNo) throws PCPSApplicationException {
		Query query = _em
				.createNativeQuery("delete from cardstock where batchno=?1 and cardseqno between to_number(?2) and to_number(?3)");
		query.setParameter(1, batchno);
		query.setParameter(2, startCardSeqNo);
		query.setParameter(3, endCardSeqNo);
		return query.executeUpdate();

	}

	/**
	 * @根据Batchno查询剩余库存
	 * @param Batchno
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public Integer getCardStockListByBatchno(String Batchno, String status) {
		String sql = "select nvl(count(*),0) from cardstock where batchno='"
				+ Batchno + "' and status='" + status + "'";
		Query query = _em.createNativeQuery(sql);
		return Integer.parseInt(query.getSingleResult().toString());
	}

	/**
	 * @根据发卡商机构号查询库存
	 * @param IssueBranchNo
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<CardStock> getCardStockListBySysIssueBranchNo(
			String IssueBranchNo) {
		String sql = "select * from cardstock where issuebranchno='"
				+ IssueBranchNo + "'";
		Query query = _em.createNativeQuery(sql, CardStock.class);
		return query.getResultList();
	}

	/**
	 * @根据发卡商机构号及状态查询库存
	 * @param IssueBranchNo
	 * @param status
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<CardStock> getCardStockListBySysIssueBranchNoAndStatus(
			String IssueBranchNo, String status) {
		String sql = "select * from cardstock where issuebranchno='"
				+ IssueBranchNo + "' and status='" + status + "'";
		Query query = _em.createNativeQuery(sql, CardStock.class);
		return query.getResultList();
	}

	/**
	 * @根据机构号，状态查询库存
	 * @param BranchNo既可以是销售也可以是制卡
	 * @param CardClassicId
	 * @param CardParValueId
	 * @param status
	 * @return
	 */
	@Override
	public List<CardStockClassicInfo> getCardStockListBySysBranchNo_Cardclassic_Parvalue_Status(
			String branchno, String status) {
		List<CardStockClassicInfo> CardStockClassicInfo = new ArrayList<CardStockClassicInfo>();
		BigInteger[] cardclassics = null;
		// 根据机构号得到该机构现有库存卡种类的集合。
		cardclassics = getCardClassicBybranchno(branchno, status);
		// 循环卡种类，取得每个卡种类对应该机构现有卡的面值，并统计该面值的张数
		for (int i = 0; i < cardclassics.length; i++) {
			BigInteger cardclassid = cardclassics[i];
			CardStockClassicInfo cci = new CardStockClassicInfo();
			cci.setCardClassicId(cardclassid);
			cci.setCardClassicName(CardClassicDaoImpl
					.getCardClassicByCardClassicId(cardclassid)
					.getCardClassicName());
			// 用得到的卡，面值，去查询库存中种类相符的卡面值的集合
			BigInteger[] CardParvalist = getCardParvalueBybranchno(branchno,
					cardclassid, status);
			// 将面值信息和张数信息放入String[]中
			List<String[]> cardstock_parvalueinfo = new ArrayList<String[]>();
			for (int j = 0; j < CardParvalist.length; j++) {
				String[] parvaluestock = new String[] {
						CardParvalist[j].toString(),
						CardParValueDaoImpl.getCardParValueByCardParValueId(
								CardParvalist[j]).getCardParValue().toString(),
						getCardStockBybranchno_parvalue_status(branchno,
								cardclassid, CardParvalist[j], status)
								.toString() };
				cardstock_parvalueinfo.add(parvaluestock);

			}
			cci.setCardstock_parvalueinfo(cardstock_parvalueinfo);
			CardStockClassicInfo.add(cci);
		}
		return CardStockClassicInfo;
	}

	/**
	 * @根据机构号,卡种类，卡面值及状态查询库存
	 * @param branchno
	 * @param cardclassid
	 * @param status
	 * @return
	 */
	@Override
	public Integer getCardStockBybranchno_parvalue_status(String branchno,
			BigInteger cardclassid, BigInteger cardparvalueid, String status) {
		String sql = "select count(*) from cardstock where batchno in (select distinct(batchno) from makecardcontrol where cardclassicid="
				+ cardclassid
				+ " and cardparvalueid="
				+ cardparvalueid
				+ " and batchno in (select distinct(batchno) from cardstock where issuebranchno='"
				+ branchno + "' and status='" + status + "'))";
		Query query = _em.createNativeQuery(sql);
		Object b = query.getSingleResult();
		return new BigInteger(b.toString()).intValue();
	}

	/**
	 * @根据机构号,卡种类，及状态查询库存卡面值
	 * @param branchno
	 * @param cardclassid
	 * @param status
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public BigInteger[] getCardParvalueBybranchno(String branchno,
			BigInteger cardclassid, String status) {
		String sql = null;
		if (SysBranchDaoImpl.getSysBranchByBranchNo(branchno).getBranchType()
				.equals(SysBranch.SYSBRANCH_ISSUEBRANCH)) {
			sql = "select distinct(cardparvalueid) from makecardcontrol where cardclassicid="
					+ cardclassid.toString()
					+ " and batchno in (select distinct(batchno) from cardstock where issuebranchno='"
					+ branchno + "' and status='" + status + "')";
		} else if (SysBranchDaoImpl.getSysBranchByBranchNo(branchno)
				.getBranchType().equals(SysBranch.SYSBRANCH_SALEBRANCH)) {
			sql = "select distinct(cardparvalueid) from makecardcontrol where cardclassicid="
					+ cardclassid.toString()
					+ " and batchno in (select distinct(batchno) from cardstock where salebranchno='"
					+ branchno + "' and status='" + status + "')";
		} else {
			return null;
		}

		Query query = _em.createNativeQuery(sql);
		List<Object> list = query.getResultList();
		BigInteger[] cardparvalues = new BigInteger[list.size()];
		for (int i = 0; i < list.size(); i++) {
			cardparvalues[i] = (((BigDecimal) list.get(i)).toBigInteger());
		}
		return cardparvalues;
	}

	/**
	 * @根据发卡商机构号及状态查询库存卡种类
	 * @param branchno
	 * @param status
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public BigInteger[] getCardClassicBybranchno(String branchno, String status) {
		String sql = null;

		if (SysBranchDaoImpl.getSysBranchByBranchNo(branchno).getBranchType()
				.equals(SysBranch.SYSBRANCH_ISSUEBRANCH)) {
			sql = "select distinct(cardclassicid) from makecardcontrol where batchno in (select distinct(batchno) from cardstock where issuebranchno='"
					+ branchno + "' and status='" + status + "')";
			PCPSUtil.Log(sql);
		} else if (SysBranchDaoImpl.getSysBranchByBranchNo(branchno)
				.getBranchType().equals(SysBranch.SYSBRANCH_SALEBRANCH)) {
			sql = "select distinct(cardclassicid) from makecardcontrol where batchno in (select distinct(batchno) from cardstock where salebranchno='"
					+ branchno + "' and status='" + status + "')";
			PCPSUtil.Log(sql);
		}

		Query query = _em.createNativeQuery(sql);
		List<Object> list = query.getResultList();
		BigInteger[] cardclassics = new BigInteger[list.size()];
		for (int i = 0; i < list.size(); i++) {
			cardclassics[i] = (((BigDecimal) list.get(i)).toBigInteger());
		}
		return cardclassics;
	}

	/**
	 * @根据销售机构号查询库存
	 * @param SaleBranchNo
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<CardStock> getCardStockListBySysSaleBranchNo(String SaleBranchNo) {
		String sql = "select * from cardstock where salebranchno='"
				+ SaleBranchNo + "'";
		Query query = _em.createNativeQuery(sql, CardStock.class);
		return query.getResultList();
	}

	/**
	 * @根据销售机构号及状态查询库存
	 * @param SaleBranchNo
	 * @param status
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<CardStock> getCardStockListBySysSaleBranchNoAndStatus(
			String SaleBranchNo, String status) {
		String sql = "select * from cardstock where salebranchno='"
				+ SaleBranchNo + "' and status='" + status + "'";
		Query query = _em.createNativeQuery(sql, CardStock.class);
		return query.getResultList();
	}

	@Override
	public void insertCardStock(CardStock CardStock)
			throws PCPSApplicationException {
		GeneralDao.insert(_em, CardStock);
	}

	/**
	 * @修改状态操作
	 * @param batchno
	 * @param startCardSeqNo
	 * @param endCardSeqNo
	 * @param status
	 */
	@Override
	public Integer updateCardStock(String batchno, String startCardSeqNo,
			String endCardSeqNo, String status) throws PCPSApplicationException {
		Query query = _em
				.createNativeQuery("update cardstock set status=?1 where batchno=?2 and cardseqno between to_number(?3) and to_number(?4)");
		query.setParameter(1, status);
		query.setParameter(2, batchno);
		query.setParameter(3, startCardSeqNo);
		query.setParameter(4, endCardSeqNo);
		return query.executeUpdate();
	}

	/**
	 * @删除
	 * @param cardStock
	 * @throws PCPSApplicationException
	 */
	public void removeCardStock(CardStock cardStock)
			throws PCPSApplicationException {

		GeneralDao.remove(_em,
				getCardStockByCardseqno(cardStock.getCardSeqNo()));
	}

	/**
	 * @修改所属机构和状态,机构可以传null
	 * @param batchno
	 * @param startCardSeqNo
	 * @param endCardSeqNo
	 * @param status
	 * @param IssueBranchNo
	 * @param SaleBranchNo
	 */
	@Override
	public Integer updateCardStockwithBranch(String batchno,
			String startCardSeqNo, String endCardSeqNo, String status,
			String IssueBranchNo, String SaleBranchNo)
			throws PCPSApplicationException {
		Query query = _em
				.createNativeQuery("update cardstock set STATUS=?,IssueBranchNo=?,SaleBranchNo=? where batchno=? and cardseqno between to_number(?) and to_number(?)");
		query.setParameter(1, status);
		query.setParameter(2, IssueBranchNo);
		query.setParameter(3, SaleBranchNo);
		query.setParameter(4, batchno);
		query.setParameter(5, startCardSeqNo);
		query.setParameter(6, endCardSeqNo);
		return query.executeUpdate();
	}

	/**
	 * @查询某销售机构序号段卡库存数量
	 * @param startCardSeqNo
	 * @param endCardSeqNo
	 * @param status
	 * @param SaleBranchNo
	 */
	@Override
	public Integer getCardStockListCountBySaleBranchNoAndStatusAndCredseqno(
			String startCardSeqNo, String endCardSeqNo, String status,
			String SaleBranchNo) {
		String sql = "select nvl(count(*),0) from cardstock where salebranchno='"
				+ SaleBranchNo
				+ "' and cardseqno between to_number('"
				+ startCardSeqNo
				+ "') and to_number('"
				+ endCardSeqNo
				+ "') and status='" + status + "'";
		Query query = _em.createNativeQuery(sql);
		return Integer.parseInt(query.getSingleResult().toString());
	}

	/**
	 * @根据机构号,起止序号，状态查询卡库存
	 * @param startCardSeqNo
	 * @param endCardSeqNo
	 * @param status
	 * @param SysBranchNo
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<CardStock> getCardStockListCountBySysbranchnoNoAndStatusAndCredseqno(
			String startCardSeqNo, String endCardSeqNo, String status,
			String SysBranchNo) {
		String sql = "select * from cardstock where  cardseqno between to_number('"
				+ startCardSeqNo
				+ "') and to_number('"
				+ endCardSeqNo
				+ "') and status='"
				+ status
				+ "' "
				+ "and (SALEBRANCHNO='"
				+ SysBranchNo + "' or IssueBranchNo='" + SysBranchNo + "')";
		Query query = _em.createNativeQuery(sql, CardStock.class);
		return query.getResultList();
	}

	/**
	 * @根据机构号,起止序号，状态查询卡库存数量
	 * @param startCardSeqNo
	 * @param endCardSeqNo
	 * @param status
	 * @param SysBranchNo
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public HashMap<String, Integer> getCardStockCountBySysbranchnoNoAndStatusAndCredseqno(
			String startCardSeqNo, String endCardSeqNo, String status,
			String SysBranchNo) {
		String sql = "select status, count(*) from cardstock where cardseqno between ?1 and ?2 "
				+ "and (SaleBranchNo=?3 or IssueBranchNo=?3) and status=?4 group by status order by status";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, startCardSeqNo);
		query.setParameter(2, endCardSeqNo);
		query.setParameter(3, SysBranchNo);
		query.setParameter(4, status);
		List<Object[]> objs = query.getResultList();
		HashMap<String, Integer> hm = new HashMap<String, Integer>();
		for (Object[] obj : objs) {
			hm.put(obj[0].toString(), Integer.parseInt(obj[1].toString()));
		}
		return hm;
	}

	/**
	 * @查询某制卡机构序号段卡库存
	 * @param startCardSeqNo
	 * @param endCardSeqNo
	 * @param status
	 * @param SaleBranchNo
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<CardStock> getCardStockListBySaleBranchNoAndStatusAndCredseqno(
			String startCardSeqNo, String endCardSeqNo, String status,
			String salebranchno) {
		String sql = "select * from cardstock where salebranchno='"
				+ salebranchno + "' and cardseqno between to_number('"
				+ startCardSeqNo + "') and to_number('" + endCardSeqNo
				+ "') and status='" + status + "'";
		Query query = _em.createNativeQuery(sql, CardStock.class);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<CardStock> getCardStockListBySaleBranchNoAndCardSeqNo(
			String startCardSeqNo, String endCardSeqNo, String saleBranchNo) {
		String sql = "select * from cardstock where SaleBranchNo='"
				+ saleBranchNo + "' and cardseqno between to_number('"
				+ startCardSeqNo + "') and to_number('" + endCardSeqNo
				+ "') order by CardSeqNo";
		Query query = _em.createNativeQuery(sql, CardStock.class);
		return query.getResultList();
	}

	/**
	 * @锁定指定机构、指定批次、指定卡序号范围、指定状态
	 * 
	 * @param batchNo
	 * @param saleBranchNo
	 * @param startCardSeqNo
	 * @param endCardSeqNo
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<CardStock> getCardStockListByFilterForUpdate(String batchNo,
			String saleBranchNo, String startCardSeqNo, String endCardSeqNo) {
		String sql = "select * from cardstock where SaleBranchNo= ?1 and batchNo = ?2 and CardSeqNo between ?3 and ?4 for update";
		Query query = _em.createNativeQuery(sql, CardStock.class);
		// System.out.println(saleBranchNo+"===="+batchNo+"====="+startCardSeqNo+"====="+endCardSeqNo);
		query.setParameter(1, saleBranchNo);
		query.setParameter(2, batchNo);
		query.setParameter(3, startCardSeqNo);
		query.setParameter(4, endCardSeqNo);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<BranchCardStock> getBranchCardStock(PCPSWebInput webInput,
			String branchno, String batchno, String status,
			BigInteger cardFaceId, Integer page) {
		// List<BranchCardStock> branchcardstocklist = new
		// ArrayList<BranchCardStock>();
		// String sql = "select (nvl(c.issuebranchno, '') || nvl(c.salebranchno,
		// '')) branchno,"
		// + "c.batchno,m.cardfaceid,cc.cardfacename,count(cardseqno) "
		// + "from cardstock c, makecardcontrol m, cardface cc "
		// + "where c.batchno = m.batchno"
		// + " and m.cardfaceid = cc.cardfaceid"
		// + " and (-1 = ?1 or (nvl(c.issuebranchno, '') || nvl(c.salebranchno,
		// '')) = ?2)"
		// + " and (-1 <> ?1 or (nvl(c.issuebranchno, '') || nvl(c.salebranchno,
		// '')) in "
		// + "(select subbranchno from SYSBRANCHSUBORD where branchno = ?2))"
		// + " and c.batchno like '%'||?3||'%'"
		// + " and (-1 = ?4 or m.cardFaceId = ?4)"
		// + " and ('-1' = ?5 or c.status = ?5) "
		// + "group by
		// c.issuebranchno,c.salebranchno,c.batchno,m.cardfaceid,cc.cardfacename
		// "
		// + "order by branchno,m.cardfaceid,c.batchno";
		if (PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
			return null;
		}
		String sqlcond1 = "";
		String sqlcond2 = "";
		if (PCPSUtil.isEmpty(branchno)) {
			branchno = webInput.getOperBranchNo();
		}
		if (!webInput.isAll()) {
			sqlcond1 = "select subBranchNo from sysbranchsubord t where t.branchno='"
					+ branchno + "' and t.SubBranchType='11'";
			sqlcond2 = "select subBranchNo from sysbranchsubord t where t.branchno='"
					+ branchno + "' and t.SubBranchType='12'";
		} else {
			sqlcond1 = branchno;
			sqlcond2 = branchno;
		}
		String sql = "";
		if (page > -1) {
			int startPosition = Config.PAGE_SIZE * page;
			int maxResult = (page + 1) * Config.PAGE_SIZE;
			sql = "select issuebranchno, salebranchno,cardfaceid,cardfacename, batchno,status,count from (select b.*, rownum rn from(select a.*,c.cardfaceid,c.cardfacename from "
					+ "(select t.issuebranchno,t.salebranchno,t.batchno,t.status,count(*) count from cardstock t where (t.issuebranchno in ("
					+ sqlcond1
					+ ") or "
					+ "t.salebranchno in ("
					+ sqlcond2
					+ ")) and (?1 is null or t.batchno=?1) and (?2 is null or t.status=?2) group by t.issuebranchno,"
					+ "t.salebranchno,t.batchno,t.status) a,makecardcontrol m,cardface c where a.batchno=m.batchno and m.cardfaceid=c.cardfaceid "
					+ "and (?3 is null or c.cardfaceid=?3))b where rownum <= "
					+ maxResult
					+ ") where rn > "
					+ startPosition
					+ " order by issuebranchno, salebranchno,cardfacename,batchno, status";
		} else {
			sql = "select issuebranchno, salebranchno,cardfaceid,cardfacename, batchno,status,count from (select b.*, rownum rn from(select a.*,c.cardfaceid,c.cardfacename from "
					+ "(select t.issuebranchno,t.salebranchno,t.batchno,t.status,count(*) count from cardstock t where (t.issuebranchno in ("
					+ sqlcond1
					+ ") or "
					+ "t.salebranchno in ("
					+ sqlcond2
					+ ")) and (?1 is null or t.batchno=?1) and (?2 is null or t.status=?2) group by t.issuebranchno,"
					+ "t.salebranchno,t.batchno,t.status) a,makecardcontrol m,cardface c where a.batchno=m.batchno and m.cardfaceid=c.cardfaceid "
					+ "and (?3 is null or c.cardfaceid=?3))b )"
					+ " order by issuebranchno, salebranchno,cardfacename,batchno, status";
		}
		Query query = _em.createNativeQuery(sql, BranchCardStock.class);
		query.setParameter(1, batchno);
		query.setParameter(2, status);
		query.setParameter(3, cardFaceId);
		// List<Object[]> objlists = query.getResultList();
		// for (Object[] objlist : objlists) {
		// BranchCardStock bcs = new BranchCardStock();
		// bcs.setBranchno((String) objlist[0]);
		// bcs.setBatchno((String) objlist[1]);
		// bcs.setCardFaceId(PCPSUtil.BigDecimalToBigInteger(objlist[2]));
		// bcs.setCardFaceName(objlist[3].toString());
		// bcs.setCardcount(((BigDecimal) objlist[4]).intValue());
		// branchcardstocklist.add(bcs);
		// }
		return query.getResultList();
	}

	@Override
	public Integer getBranchCardStockCount(PCPSWebInput webInput,
			String branchno, String batchno, String status,
			BigInteger cardFaceId) {
		// String sql = "select nvl(count(*),0) from(select
		// (nvl(c.issuebranchno, '') || nvl(c.salebranchno, ''))
		// branchno,c.batchno,m.cardfaceid,cc.cardfacename,count(cardseqno) "
		// + " from cardstock c, makecardcontrol m, cardface cc "
		// + " where c.batchno = m.batchno"
		// + " and m.cardfaceid = cc.cardfaceid"
		// + " and (-1 = ?1 or (nvl(c.issuebranchno, '') || nvl(c.salebranchno,
		// '')) = ?2)"
		// + " and (-1 <> ?1 or (nvl(c.issuebranchno, '') || nvl(c.salebranchno,
		// '')) in "
		// + " (select subbranchno from SYSBRANCHSUBORD where branchno = ?2))"
		// + " and c.batchno like '%'||?3||'%'"
		// + " and (-1 = ?4 or m.cardFaceId = ?4)"
		// + " and ('-1' = ?5 or c.status = ?5) "
		// + "group by
		// c.issuebranchno,c.salebranchno,c.batchno,m.cardfaceid,cc.cardfacename
		// order by branchno)";
		if (PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
			return null;
		}
		String sqlcond1 = "";
		String sqlcond2 = "";
		if (PCPSUtil.isEmpty(branchno)) {
			branchno = webInput.getOperBranchNo();
		}
		if (!webInput.isAll()) {
			sqlcond1 = "select subBranchNo from sysbranchsubord t where t.branchno='"
					+ branchno + "' and t.SubBranchType='11'";
			sqlcond2 = "select subBranchNo from sysbranchsubord t where t.branchno='"
					+ branchno + "' and t.SubBranchType='12'";
		} else {
			sqlcond1 = branchno;
			sqlcond2 = branchno;
		}
		String sql = "select count(*) from (select t.issuebranchno,t.salebranchno,t.batchno,t.status,count(*) count from cardstock t where "
				+ "(t.issuebranchno in ("
				+ sqlcond1
				+ ") or t.salebranchno in ("
				+ sqlcond2
				+ ")) and (?1 is null or t.batchno=?1) and (?2 is null or t.status=?2) "
				+ "group by t.issuebranchno,t.salebranchno,t.batchno,t.status) a,MakeCardControl m,cardface c where a.batchno=m.batchno and m.cardfaceid=c.cardfaceid and (?3 is null or c.cardfaceid=?3)";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, batchno);
		query.setParameter(2, status);
		query.setParameter(3, cardFaceId);
		return Integer.parseInt(query.getSingleResult().toString());
	}

	/**
	 * 卡库存详细信息查询，输入批次号，机构号，返回具体卡号段信息
	 * 
	 * @param 批次号
	 * @param 机构号
	 * @param 状态
	 *            -1表示查全部
	 * @return 卡序号段集合
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<CardStock> getCardStockDetail(String batchno, String branchno,
			String status, Integer page) {
		int maxResult = Config.PAGE_SIZE;
		int startPosition = maxResult * page;
		List<CardStock> cardstockdetail = new ArrayList<CardStock>();
		String sql = "with v as(select c.cardseqno,"
				+ "lag(c.cardseqno) over(order by c.cardseqno) as lag,"
				+ "lead(c.cardseqno) over(order by c.cardseqno) as lead "
				+ "from cardstock c where c.batchno =?1 "
				+ "and (nvl(c.issuebranchno,'')||nvl(c.salebranchno,''))=?2 and ('-1'=?3 or c.status=?3) order by c.cardseqno) "
				+ "select v1.cardseqno, v2.cardseqno  from (select v.cardseqno, rownum as rn "
				+ "from v " + "where (v.cardseqno - v.lag) != 1 "
				+ "or v.lag is null) v1, "
				+ "(select v.cardseqno, rownum as rn " + "from v  "
				+ "where (v.lead - v.cardseqno) != 1   "
				+ "or v.lead is null) v2 where v1.rn = v2.rn";
		Query query = _em.createNativeQuery(sql).setFirstResult(startPosition)
				.setMaxResults(maxResult);
		query.setParameter(1, batchno);
		query.setParameter(2, branchno);
		query.setParameter(3, status);
		CardStock cs = null;
		List<Object[]> objlists = query.getResultList();
		for (Object[] objlist : objlists) {
			cs = new CardStock();
			cs.setCardSeqNo(objlist[0].toString());
			cs.setCardNo(objlist[1].toString());
			if (objlist[1].toString().trim().length() < 18) {
				cs.setCardPwd(Integer.parseInt(objlist[1].toString().substring(
						6, 12))
						- Integer.parseInt(objlist[0].toString().substring(6,
								12)) + 1 + "");
			} else {
				cs.setCardPwd(Integer.parseInt(objlist[1].toString().substring(
						12, 18))
						- Integer.parseInt(objlist[0].toString().substring(12,
								18)) + 1 + "");
			}
			cardstockdetail.add(cs);
		}
		return cardstockdetail;
	}

	/**
	 * 卡库存详细信息查询，输入批次号，机构号，返回具体卡号段信息
	 * 
	 * @param 批次号
	 * @param 机构号
	 * @param 状态
	 *            -1表示查全部
	 * @return 卡序号段集合总数
	 */
	@Override
	public BigInteger getCardStockDetailTotal(String batchno, String branchno,
			String status) {
		String sql = "with v as(select c.cardseqno,"
				+ "lag(c.cardseqno) over(order by c.cardseqno) as lag,"
				+ "lead(c.cardseqno) over(order by c.cardseqno) as lead "
				+ "from cardstock c where c.batchno =?1 "
				+ "and (nvl(c.issuebranchno,'')||nvl(c.salebranchno,''))=?2 and ('-1'=?3 or c.status=?3) order by c.cardseqno) "
				+ "select nvl(count(*),0) from (select v.cardseqno, rownum as rn "
				+ "from v " + "where (v.cardseqno - v.lag) != 1 "
				+ "or v.lag is null) v1, "
				+ "(select v.cardseqno, rownum as rn " + "from v  "
				+ "where (v.lead - v.cardseqno) != 1   "
				+ "or v.lead is null) v2 where v1.rn = v2.rn";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, batchno);
		query.setParameter(2, branchno);
		query.setParameter(3, status);
		return new BigInteger(query.getSingleResult().toString());
	}

	/**
	 * 按卡号/卡序号检索卡库存
	 * 
	 * @param cardNo
	 *            卡号或卡序号
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public CardStock getCardStockByCardNoForUpdate(String cardNo) {
		String sql = "select t.* from cardstock t where t.cardSeqNo = ?1 or t.cardNo = ?1 for update";
		Query query = _em.createNativeQuery(sql, CardStock.class);
		query.setParameter(1, cardNo);
		List<CardStock> list = query.getResultList();
		if (list == null || list.size() != 1)
			return null;
		CardStock cs = list.get(0);
		_em.refresh(cs);
		return getCardStockByCardseqno(cs.getCardSeqNo());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<CardStock> getCardStockListByBatchNo(String issueBranchNo,
			String batchNo) {
		String sql = "select * from CardStock " + " where IssueBranchNo = ?1 "
				+ " and BatchNo = ?2 " + " order by CARDSEQNO asc ";
		Query query = _em.createNativeQuery(sql, CardStock.class);
		query.setParameter(1, issueBranchNo);
		query.setParameter(2, batchNo);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getCardStockList() {
		String sql = "select IssueBranchNo,BatchNo from cardstock "
				+ " group by IssueBranchNo,BatchNo "
				+ " order by IssueBranchNo,BatchNo asc ";
		Query query = _em.createNativeQuery(sql);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getCardStockCountBySalesCardOrderId(
			BigInteger salesGetCardOrderId) {
		String sql = "select CardSeqNo,CardNo from CardStock where Status='"
				+ CardStock.CARDSTOCK_STATUS_INTRANSIT
				+ "' and SalesGetCardOrderId=" + salesGetCardOrderId;
		Query query = _em.createNativeQuery(sql);
		return query.getResultList();
	}

	@Override
	public void updateCardStockForSalesGetCardOrder(String batchno,
			String startCardSeqNo, String endCardSeqNo, String status,
			BigInteger salesGetCardOrderId) throws PCPSApplicationException {
		Query query = _em
				.createNativeQuery("update cardstock set status=?1,salesgetcardorderid=?5"
						+ " where batchno=?2 and cardseqno between to_number(?3) and to_number(?4)");
		query.setParameter(1, status);
		query.setParameter(2, batchno);
		query.setParameter(3, startCardSeqNo);
		query.setParameter(4, endCardSeqNo);
		query.setParameter(5, salesGetCardOrderId == null ? ""
				: salesGetCardOrderId);
		query.executeUpdate();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getCardStockCountBySalesGetCardOrderId(
			BigInteger salesGetCardOrderId, int orderItemSeqNo) {
		String sql = "select * from"
				+ " (select nvl(count(*),0) from cardstock t,salesgetcardorderdetail s"
				+ " where t.salesgetcardorderid=s.salesgetcardorderid"
				+ " and t.cardseqno between to_number(s.cardstartseqno) and to_number(s.cardendseqno)"
				+ " and t.salesgetcardorderid = ?1 and s.orderitemseqno = ?2 and t.status = 1"
				+ " )a,"
				+ " (select nvl(count(*),0) from cardstock t,salesgetcardorderdetail s"
				+ " where t.salesgetcardorderid=s.salesgetcardorderid "
				+ " and t.cardseqno between to_number(s.cardstartseqno) and to_number(s.cardendseqno)"
				+ " and s.salesgetcardorderid = ?1 and s.orderitemseqno = ?2 and t.status = 0"
				+ " )b";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, salesGetCardOrderId);
		query.setParameter(2, orderItemSeqNo);
		return query.getResultList();
	}

	// 卡库存报表1 返回 1机构号 2张数
	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getCardStockReportOne(PCPSWebInput webInput,
			String branchNo) {
		String sqlCond1 = PCPSUtil.getSqlCond(webInput, branchNo,
				"issuebranchno");
		String sqlCond2 = PCPSUtil.getSqlCond(webInput, branchNo,
				"salebranchno");
		String sql = "select a.branchno,"
				+ " (select count(*) from CardStock"
				+ " where ((issuebranchno is not null and issuebranchno=a.branchno) or "
				+ " (salebranchno is not null and salebranchno=a.branchno))"
				+ " and status = '0') count" + " from ( "
				+ " select distinct(t.issuebranchno) branchno"
				+ " from CardStock t"
				+ " where issuebranchno is not null and t.status = '0' "
				+ sqlCond1 + " union "
				+ " select distinct(t.salebranchno) branchno"
				+ " from CardStock t"
				+ " where salebranchno is not null and t.status = '0'"
				+ sqlCond2 + " )a order by branchno";
		Query query = _em.createNativeQuery(sql);
		return query.getResultList();
	}

	// 卡库存报表2 返回 1卡名称 2张数 3批次号
	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getCardStockReportTwo(String branchNo) {
		String sql = "select (select c.cardfacename"
				+ " from makecardcontrol m,cardface c"
				+ " where m.cardfaceid=c.cardfaceid and a.batchno=m.batchno)cardfacename,"
				+ "(select count(*)"
				+ " from CardStock"
				+ " where (issuebranchno=?1 or salebranchno=?1) and batchno=a.batchno and status = '0') count,a.batchno"
				+ " from "
				+ "(select distinct(t.batchno) batchno"
				+ " from CardStock t"
				+ " where (t.issuebranchno=?1 or t.salebranchno=?1) and t.status = '0')a"
				+ " order by cardfacename";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, branchNo);
		return query.getResultList();
	}

	// 卡库存报表3 返回 1卡名称 2张数
	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getCardStockReportThird(String batchno,
			String branchno) {
		String sql = "with v as("
				+ " select c.cardseqno,"
				+ " lag(c.cardseqno) over(order by c.cardseqno) as lag,"
				+ " lead(c.cardseqno) over(order by c.cardseqno) as lead"
				+ " from cardstock c"
				+ " where c.batchno =?1"
				+ " and (nvl(c.issuebranchno,'')||nvl(c.salebranchno,''))=?2"
				+ " and ('-1'=?3 or c.status=?3)"
				+ " order by c.cardseqno)"
				+ " select v1.cardseqno, v2.cardseqno"
				+ " from"
				+ " (select v.cardseqno, rownum as rn from v where (v.cardseqno - v.lag) != 1 or v.lag is null) v1,"
				+ " (select v.cardseqno, rownum as rn from v where (v.lead - v.cardseqno) != 1 or v.lead is null) v2"
				+ " where v1.rn = v2.rn";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, batchno);
		query.setParameter(2, branchno);
		query.setParameter(3, CardStock.CARDSTOCK_STATUS_NORMAL);
		List<Object[]> objlists = query.getResultList();
		return objlists;
	}

}
